package model.MySQL;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.EqActiveCooling;
import model.EqCO2;
import model.EqCurtain;
import model.EqLight;
import model.EqSprinkler;
import model.EqTermostat;
import model.EqWindow;
import model.Equipment;
import model.House;
import model.ModelClass;
import model.Plant;
import model.Section;
/**
 * MysqlData is where all the communication with the database occur. 
 * Here the data will loaded from the database into the system.
 * Here the data will be uploaded from the system to the database.  
 * @author Sylvi, Sune, Bergur og Roi
 *
 */
public class MySQLData {
	Connection connection = null;
	Statement statement = null;
	ResultSet resultSet = null;
	//String DATABASE_URL  = "jdbc:mysql://localhost/ebank";
	String DATABASE_URL = "jdbc:mysql://213.239.193.108:3306/FutureGarden";
	private PreparedStatement insertHouse = null;
	private PreparedStatement resetAutoincrement = null;
	private PreparedStatement insertSection = null;
	private PreparedStatement insertEquipment = null;
	private PreparedStatement insertPlantType = null;
	private PreparedStatement insertAddedPlant = null;
	private PreparedStatement deleteAddedPlant = null;
	
	private PreparedStatement deleteAccountWithCPRNr = null;
	private PreparedStatement deleteAccountWithAccountNr = null;
	private PreparedStatement deleteTransactions = null;
	private PreparedStatement editCustomer = null;
	
	/**
	 */
	ModelClass modelClass;
	
	/*
	 * the Constructor of the mysqlData
	 */
	public MySQLData(ModelClass modelClass) {
		this.modelClass = modelClass;
		try {
			connection = DriverManager.getConnection(DATABASE_URL,"futuregarden","gruppe12");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
	}
	/*
	 * Creates a Connection to the database
	 * @return
	 */
//	public Connection getConnection(){
//		try {
//			
//			//connection = DriverManager.getConnection(DATABASE_URL,"root","");
//
//			connection = DriverManager.getConnection(DATABASE_URL,"futuregarden","gruppe12");
//
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		return connection;
//	}
	
	/*
	 * returns the resultset
	 */
	public ResultSet getResultSet(Connection connection, String qeury){
	
	try {
		statement = connection.createStatement();
		resultSet = statement.executeQuery(qeury);
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return resultSet;
	}
	/*
	 * returns the ResultSetMetaData
	 */
	public ResultSetMetaData getResultSetMetaData(ResultSet resultSet){
		ResultSetMetaData metaData = null;
		try {
			metaData = resultSet.getMetaData();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return metaData;
	}
	/*
	 * Method which will add all the data, which is stored in mysql, in to the program
	 */
	public void addAllData(){
//		Connection connection = getConnection();
		//gets the information from the customer table
		ResultSet resultSet = getResultSet(connection,"select * from Houses");
		ResultSetMetaData metaData = getResultSetMetaData(resultSet);
		int numberOfColums;
		try {
			numberOfColums = metaData.getColumnCount();
			String data[] = new String[numberOfColums];
			
			//System.out.println("User1 Of the Bank: \n");
			for (int i = 1; i<=numberOfColums;i++){
				//System.out.printf("%-8s\t",metaData.getColumnName(i));
			}
			//System.out.println();
			//add the customer sql data into the program
			while(resultSet.next()){
				for(int i = 1;i<=numberOfColums;i++){
					//System.out.printf("%-8s\t", resultSet.getObject(i));
					data[i-1] = ""+ resultSet.getObject(i);
					System.out.println(data[i-1]);
				}	
				modelClass.addHouse(Integer.parseInt(data[0]),data[1],false);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		resultSet = getResultSet(connection,"select * from sections");
		metaData = getResultSetMetaData(resultSet);
		try {
			numberOfColums = metaData.getColumnCount();
			String data[] = new String[numberOfColums];
			
			//System.out.println("User1 Of the Bank: \n");
			for (int i = 1; i<=numberOfColums;i++){
				//System.out.printf("%-8s\t",metaData.getColumnName(i));
			}
			//System.out.println();
			//add the customer sql data into the program
			ArrayList<Section> tempSections = new ArrayList<Section>();
			while(resultSet.next()){
				for(int i = 1;i<=numberOfColums;i++){
					//System.out.printf("%-8s\t", resultSet.getObject(i));
					data[i-1] = ""+ resultSet.getObject(i);
				}
			Section section = new Section(modelClass, Integer.parseInt(data[0]), Integer.parseInt(data[1]), data[2]);
			tempSections.add(section);
			modelClass.getHouseId(section.getHouseId()).addSections(tempSections, false);
			tempSections.clear();
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		resultSet = getResultSet(connection,"select * from equipments");
		metaData = getResultSetMetaData(resultSet);
		try {
			numberOfColums = metaData.getColumnCount();
			String data[] = new String[numberOfColums];
			
			//System.out.println("User1 Of the Bank: \n");
			for (int i = 1; i<=numberOfColums;i++){
				//System.out.printf("%-8s\t",metaData.getColumnName(i));
			}
			//System.out.println();
			//add the customer sql data into the program
			while(resultSet.next()){
				for(int i = 1;i<=numberOfColums;i++){
					//System.out.printf("%-8s\t", resultSet.getObject(i));
					data[i-1] = ""+ resultSet.getObject(i);
					
				}	
			Equipment equipment = null;
			switch (data[1]) {
			case "Active Cooling":
				equipment = new EqActiveCooling();
				break;
			case "CO2":
				equipment = new EqCO2();
				break;
			case "Curtain":
				equipment = new EqCurtain();
				break;
			case "Light":
				equipment = new EqLight();
				break;
			case "Sprinkler":
				equipment = new EqSprinkler();
				break;
			case "Termostats":
				equipment  = new EqTermostat();
				break;
			case "Windows":
				equipment = new EqWindow();
			break;
		}
				modelClass.getHouseId(Integer.parseInt(data[0])).addEquipment(equipment, false);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		resultSet = getResultSet(connection,"select * from plantTypes");
		metaData = getResultSetMetaData(resultSet);
		try {
			numberOfColums = metaData.getColumnCount();
			String data[] = new String[numberOfColums];
			
			//System.out.println("User1 Of the Bank: \n");
			for (int i = 1; i<=numberOfColums;i++){
				//System.out.printf("%-8s\t",metaData.getColumnName(i));
			}
			//System.out.println();
			//add the customer sql data into the program
			while(resultSet.next()){
				for(int i = 1;i<=numberOfColums;i++){
					//System.out.printf("%-8s\t", resultSet.getObject(i));
					data[i-1] = ""+ resultSet.getObject(i);
			}
				Plant plant = new Plant(modelClass, Integer.parseInt(data[0]), data[1], Integer.parseInt(data[2]),Integer.parseInt( data[3])
						, Integer.parseInt( data[4]), Integer.parseInt( data[5]),Integer.parseInt( data[6]), data[7]);
				modelClass.addPlant(plant,false);
				
			}
			} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		resultSet = getResultSet(connection,"select * from addedPlants");
		metaData = getResultSetMetaData(resultSet);
		try {
			numberOfColums = metaData.getColumnCount();
			String data[] = new String[numberOfColums];
			
			//add the customer sql data into the program
			while(resultSet.next()){
				for(int i = 1;i<=numberOfColums;i++){
					//System.out.printf("%-8s\t", resultSet.getObject(i));
					data[i-1] = ""+ resultSet.getObject(i);
			}
				Plant tempPlant = modelClass.getPlantId(Integer.parseInt(data[1]));
				Plant newPlant = new Plant(modelClass, tempPlant.getTypeID(), tempPlant.getName(),
						tempPlant.getOptCO2(), tempPlant.getOptTemp(), tempPlant.getOptFugt(),
						tempPlant.getOptLigt(), tempPlant.getPrice(), tempPlant.getPictureLocation());
				newPlant.setId(Integer.parseInt(data[0]));
				modelClass.getSectionId(Integer.parseInt(data[2])).setPlant(newPlant, false);
				
			}
			} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
public int getHighestHouseId(){
//	Connection connection = getConnection();
	//gets the information from the customer table
	ResultSet resultSet = getResultSet(connection,"SHOW TABLE STATUS LIKE  'houses'");
	int highestId = 0;
	try {
		resultSet.next();
		highestId = Integer.parseInt(""+resultSet.getObject(11));
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	return highestId;
}
public int getHighestSectionId(){
	//gets the information from the customer table
	ResultSet resultSet = getResultSet(connection,"SHOW TABLE STATUS LIKE  'sections'");
	int highestId = 0;
	try {
		resultSet.next();
		highestId = Integer.parseInt(""+resultSet.getObject(11));
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return highestId;
}
public int getHighestPlantId(){
	//gets the information from the customer table
	ResultSet resultSet = getResultSet(connection,"SHOW TABLE STATUS LIKE  'addedPlants'");
	int highestId = 0;
	try {
		resultSet.next();
		highestId = Integer.parseInt(""+resultSet.getObject(11));
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return highestId;
}


public void uploadHouse(String name){	
		// The prepared statements are defined
	try {
			insertHouse = connection.prepareStatement( 
		            "INSERT INTO houses " + 
		            "(name ) " + 
		            "VALUES ( ?)" );
	} catch (SQLException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
	}
	try 
      {
		insertHouse.setString( 1, name );
        insertHouse.executeUpdate(); 
         
      } // end try
      catch ( SQLException sqlException )
      {
         sqlException.printStackTrace();
        // close();
      } // end catch

}

public void uploadSection(ArrayList<Section> sections){
	try {
		insertSection = connection.prepareStatement( 
	            "INSERT INTO sections " + 
	            "(houseid,name ) " + 
	            "VALUES ( ?,?)" );
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try 
  {
	for(Section section:sections){
	insertSection.setInt(1, section.getHouseId());
	insertSection.setString(2, section.getName());
	insertSection.addBatch();
	}
	insertSection.executeBatch();
	
  } // end try

  catch ( SQLException sqlException )
  {
     sqlException.printStackTrace();
    // close();
  } // end catch

}
public void uploadEquipment(int id, String type) {
	try {
		insertEquipment = connection.prepareStatement( 
	            "INSERT INTO equipments " + 
	            "(houseid,type ) " + 
	            "VALUES ( ?,?)" );
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try 
  {
	insertEquipment.setInt(1, id);
	insertEquipment.setString(2, type);
	insertEquipment.executeUpdate(); 
  } // end try
  catch ( SQLException sqlException )
  {
     sqlException.printStackTrace();
    // close();
  } // end catch
	
}
public void uploadPlantType(Plant plant) {
	try {
		insertPlantType = connection.prepareStatement( 
	            "INSERT INTO planttypes " + 
	            "(Name,optCO2,optTemp,optHumd,optLight,price,picturelocation ) " + 
	            "VALUES ( ?,?,?,?,?,?,?)" );
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try 
  {
	insertPlantType.setString(1, plant.getName());
	insertPlantType.setInt(2, plant.getOptCO2());
	insertPlantType.setInt(3, plant.getOptTemp());
	insertPlantType.setInt(4, plant.getOptFugt());
	insertPlantType.setInt(5, plant.getOptLigt());
	insertPlantType.setInt(6, plant.getPrice());
	insertPlantType.setString(7, plant.getPictureLocation());
	insertPlantType.executeUpdate(); 
  } // end try
  catch ( SQLException sqlException )
  {
     sqlException.printStackTrace();
    // close();
  } // end catch
	
}
public void uploadAddedPlant(int plantTypeID, int sectionID){
	try {
		insertPlantType = connection.prepareStatement( 
	            "INSERT INTO addedPlants " + 
	            "(PlantTypeId,SectionId) " + 
	            "VALUES ( ?,?)" );
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try 
  {
	insertPlantType.setInt(1, plantTypeID);
	insertPlantType.setInt(2, sectionID);
	insertPlantType.executeUpdate(); 
  } // end try
  catch ( SQLException sqlException )
  {
     sqlException.printStackTrace();
    // close();
  } // end catch
}
public void deleteAddedPlant(int id){
	System.out.println(id);		
	try {
				deleteAccountWithAccountNr = connection.prepareStatement(
						"DELETE FROM `addedplants` " + 
						"WHERE id = ?" );
				deleteAccountWithAccountNr.setInt(1, id);
				deleteAccountWithAccountNr.executeUpdate(); 
		} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		}
}
}
	